package com.sino.second.dao.hibernate;

import java.sql.SQLException;
import java.util.Collection;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import com.sino.bean.TblZhuanjiarukuxinxibiao;
import com.sino.second.bean.TblSystemparam;
import com.sino.second.bean.TblXmjg1;
import com.sino.second.dao.IExpertsReviewDAO;
import com.sino.util.page.AbstractPagerManager;
import com.sino.util.page.PagerModel;

public class Review2DAO extends AbstractPagerManager implements
		IExpertsReviewDAO {

	public List findReviewXmList(String xkId) {
		// TODO Auto-generated method stub
		return null;
	}

	public List findReviewXmListByZjId(Integer zjid, String hqlWhere) {
		String str_SQL = "select tx2.basicinfo,tx2.sysXmpsdj,sum(tx2.xmfs) as xmfs,sum(tx2.fzqz) as fzqz from TblXmfs2 tx2 where tx2.tblZhuanjiarukuxinxibiao.FBh=? ";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		str_SQL += "  group by tx2.basicinfo.zlid,tx2.sysXmpsdj order by sum(tx2.fzqz) desc";
		return this.getHibernateTemplate().find(str_SQL, new Object[] { zjid });
	}

	public Object findReviewResultsByZlid(String zlid) {
		String str_SQL = "from TblXmjg2 txj2 where txj2.basicinfo.zlid=?";
		List txj2List = this.getHibernateTemplate().find(str_SQL,
				new Object[] { zlid });
		if (txj2List != null && txj2List.size() >= 1) {
			return txj2List.get(0);
		}
		return null;
	}

	public List findReviewResultsList(String hqlWhere) {
		String str_SQL = "from TblXmjg2 txj2 where '1'='1' ";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		str_SQL += " order by txj2.xmpjpxfs desc";
		return this.getHibernateTemplate().find(str_SQL);

	}

	public PagerModel findReviewResultsPageList(String hqlWhere) {
		String str_SQL = "from TblXmjg2 txj2 where '1'='1' ";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		str_SQL += " order by txj2.basicinfo.xk,txj2.xmpjpxfs desc";
		return this.searchPaginated(str_SQL);
	}

	public void saveReviewsResults(Object o) {
		this.getHibernateTemplate().save(o);
	}

	public List findReviewXmListByZlId(String zlid, String hqlWhere) {
		String str_SQL = "select tx2.tblZhuanjiarukuxinxibiao,tx2.sysXmpsdj, tx2.basicinfo.zlid,sum(tx2.xmfs) as xmfs,sum(tx2.fzqz) from TblXmfs2 tx2 where tx2.basicinfo.zlid=? ";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		str_SQL += "  group by tx2.tblZhuanjiarukuxinxibiao.FBh ,tx2.sysXmpsdj order by sum(tx2.fzqz)";
		return this.getHibernateTemplate().find(str_SQL, new Object[] { zlid });
	}

	public List findReviewsList(String zlid, Integer zjid) {
		String str_SQL = "from TblXmfs2 tx2 where tx2.basicinfo.zlid=? and tx2.tblZhuanjiarukuxinxibiao.FBh=? order by tx2.sysXmpszb.id";
		return this.getHibernateTemplate().find(str_SQL,
				new Object[] { zlid, zjid });
	}

	public void saveOrUpdateReviews(Collection o) {
		this.getHibernateTemplate().saveOrUpdateAll(o);

	}

	public int findReviewCount(String hqlWhere) {
		String str_SQL = "select count(*)  from  TblXmfs2 tx2 where '1'='1'";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}

		return (Integer) this.getHibernateTemplate().find(str_SQL).get(0);
	}

	public List findReviewXmList(final int firstRow, final int maxRow,
			final String xkId, final String hqlWhere) {
		String str_SQL = "from TblXmjg1 txj1 where  txj1.basicinfo.xk.id=?";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		final String queryhql = str_SQL;
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(queryhql);
				query.setFirstResult(firstRow);
				query.setMaxResults(maxRow);
				query.setString(0, xkId);
				List<TblXmjg1> list = (List<TblXmjg1>) query.list();
				return list;
			}
		});

	}

	/**
	 * 专家列表记录数
	 * 
	 * @param xk
	 *            学科
	 * @param xkId
	 *            专利ID
	 * @return
	 */
	public int findReviewXmCount(String xkId, String hqlWhere) {
		String str_SQL = "select count(*) from TblXmjg1 txj1 where '1'='1' ";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		return (Integer) this.getHibernateTemplate().find(str_SQL).get(0);
	}

	/**
	 * 查找复审分数表中的数据
	 */
	public List<?> findReviewXmFsList(String hqlWhere) {
		String hql = "select xmfs.basicinfo.zlid,xmfs.basicinfo.name,xmfs.basicinfo.zlqr,xmfs.basicinfo.xk.name,"
				+ // 项目id，项目name,项目专利权人,项目学科
				"count(distinct xmfs.tblZhuanjiarukuxinxibiao),"
				+ // 已评专家数
				"sum(xmfs.xmfs),"
				+ // 项目总分
				"sum(xmfs.xmfs)/count(distinct xmfs.tblZhuanjiarukuxinxibiao),"
				+ // 项目平均分
				"sum(xmfs.fzqz),"
				+ // 项目分值权重总分
				"sum(xmfs.fzqz)/count(distinct xmfs.tblZhuanjiarukuxinxibiao),"
				+ // 项目分值权重平均分
				"xmfs.year,xmfs.state " + "from TblXmfs2 xmfs ";
		if ("" != hqlWhere && hqlWhere.length() > 0) {
			hql = hql + "where " + hqlWhere;
		}

		hql = hql + " group by xmfs.basicinfo order by sum(xmfs.fzqz) desc";
		return this.getHibernateTemplate().find(hql);
	}

	/**
	 * 查找复审分数表中的记录数
	 */
	public int findCountReviewXmFsList(String hqlWhere) {
		String hql = "select count(distinct xmfs2.basicinfo)"
				+ "from TblXmfs2 xmfs2 ";
		if ("" != hqlWhere && hqlWhere.length() > 0) {
			hql = hql + "where " + hqlWhere;
		}
		return (Integer) this.getHibernateTemplate().find(hql).get(0);
	}

	public Object findSumFenshu(String zlid) {
		// TODO Auto-generated method stub
		return null;
	}

	public int findCountZhuanjiaByZlid(String zlid) {
		// TODO Auto-generated method stub
		return 0;
	}

	public Object findSumFenshuByZlid(String zlid) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<TblXmjg1> findReviewXmJgList(String hqlWhere) {
		// TODO Auto-generated method stub
		return null;
	}

	public List findZhuanjiaByZlid(String zlid) {
		// TODO Auto-generated method stub
		return null;
	}

	public void updateReviewsResults(Object o) {
		this.getHibernateTemplate().update(o);

	}

	/**
	 * 更新复审分数表中的审核状态
	 */
	public void updateReviewsState(String basicInfoId, String state) {
		if (null != this.findReviewResultsByZlid(basicInfoId)) {
			String hql = "update TblXmfs2 xmfs set xmfs.state=? where xmfs.basicinfo.zlid=?";
			this.getSession().createQuery(hql).setParameter(0, state)
					.setParameter(1, basicInfoId).executeUpdate();
		}
	}

	/**
	 * 根据项目id查找复审复审表的已评专家信息
	 */
	@SuppressWarnings("unchecked")
	public List<TblZhuanjiarukuxinxibiao> listReviewExpertByBasicInfoId(
			String basicInfoId) {
		String hql = "select xmfs.tblZhuanjiarukuxinxibiao from TblXmfs2 xmfs where xmfs.basicinfo.zlid=? group by xmfs.tblZhuanjiarukuxinxibiao";

		return (List<TblZhuanjiarukuxinxibiao>) this.getSession().createQuery(
				hql).setParameter(0, basicInfoId).list();
	}

	public Object findStateByZlid(String zlid) {
		// TODO Auto-generated method stub
		return 0;
	}

	public Object findXmByZlid(String zlid) {
		String str_SQL = "select tx2.basicinfo," + // 项目信息
				"sum(tx2.xmfs)," + // 总分
				"sum(tx2.xmfs)/count(distinct tx2.tblZhuanjiarukuxinxibiao)," + // 平均分
				"sum(tx2.fzqz)," + // 分值权重总分
				"sum(tx2.fzqz)/count(distinct tx2.tblZhuanjiarukuxinxibiao) " + // 分值权重平均分
				"from TblXmfs2 tx2 where tx2.basicinfo.zlid=? ";
		str_SQL += "  group by tx2.basicinfo";
		return this.getSession().createQuery(str_SQL).setParameter(0, zlid).uniqueResult();
	}

	public Integer findZjpsfpCountByXmpsmcId(String xmpsmcId, String zhuanyeId,
			String hqlWhere) {
		// TODO Auto-generated method stub
		return null;
	}

	public List listReviewFsList(String zlid, String hqlWhere) {
		String str_SQL = "select " + "tx2.tblZhuanjiarukuxinxibiao," + // 专家信息
				"tx2.sysXmpsdj, " + // 建议等级
				"sum(tx2.xmfs)," + // 总分
				"sum(tx2.xmfs)/count(distinct tx2.tblZhuanjiarukuxinxibiao)," + // 平均分
				"sum(tx2.fzqz)," + // 分值权重总分
				"sum(tx2.fzqz)/count(distinct tx2.tblZhuanjiarukuxinxibiao) " + // 分值权重平均分
				"from TblXmfs2 tx2 where tx2.basicinfo.zlid=? ";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		str_SQL += "  group by tx2.tblZhuanjiarukuxinxibiao order by sum(tx2.fzqz) desc";
		return this.getHibernateTemplate().find(str_SQL, new Object[] { zlid });
	}

	

	@SuppressWarnings("unchecked")
	public List<Integer> listReviewFsExpertIdList(String hqlWhere) {
		String hql = "select distinct xmfs.tblZhuanjiarukuxinxibiao.FBh from TblXmfs2 xmfs ";
		if ("" != hqlWhere && hqlWhere.length() > 0) {
			hql = hql + "where " + hqlWhere;
		}
		return (List<Integer>) this.getHibernateTemplate().find(hql);
	}

	public List listReviewXmIdList(int expertId, String hqlWhere) {
		String hql="select xmfs.basicinfo.zlid,sum(xmfs.fzqz),sum(xmfs.xmfs) from TblXmfs2 xmfs where xmfs.tblZhuanjiarukuxinxibiao.FBh=? ";
		if ("" != hqlWhere && hqlWhere.length() > 0) {
			hql = hql + "and " + hqlWhere;
		}
		hql=hql+" group by xmfs.basicinfo order by sum(xmfs.fzqz) desc";
		return this.getHibernateTemplate().find(hql, expertId);
	}

	
	public void saveOrUpdateReviews1(Object o) {
		// TODO Auto-generated method stub
		
	}

	public Integer findXmCountByXkId(String xkId) {
		// TODO Auto-generated method stub
		return null;
	}

	public Integer findFsCountByXkId(String xkId) {
		// TODO Auto-generated method stub
		return null;
	}

	public void saveOrUpdateTime(TblSystemparam t,TblSystemparam t2) {
		// TODO Auto-generated method stub
		
	}
}